package DBLayer;

import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

public class DBProduct implements IFDBProduct
{
	private Connection con;
	
	public DBProduct()
	{
		con = DbConnection.getInstance().getDBcon();
	}

	@Override
	public ArrayList<Product> getAllProducts(boolean retrieveAssociation) 
	{
		return miscWhere("", retrieveAssociation);
	}

	@Override
	public Product findProduct(int id, boolean retrieveAssociation) 
	{
		String wClause = " productID = '" + id + "'";
		return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	public Product searchProductDescription(String attValue, boolean retrieveAssociation)
	{
		String wClause = "description like '%" + attValue + "%'";
		System.out.println("SearchProduct " + wClause);
		return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	public int insertProduct(Product prod) throws Exception 
	{
		int nextId = GetMax.getMaxId("Select max(productID) from product");
		nextId++;
		System.out.println("next productID = " + nextId);
		int rc = -1;
		String query = "INSERT INTO product(name, description, status, storage, price, supplierID, clothes_size, clothes_colour, equipment_type, equipment_desc, gunRep_material, gunRep_caliber) VALUES('"
				+ prod.getName()+"','"
				+ prod.getDescription() + "','"
				+ prod.getStatus() + "','"
				+ prod.getStorage() + "','"
				+ prod.getPrice() + "','"
				+ String.valueOf(prod.getSupplier().getId()) + "','"
				+ prod.getClothes_size() + "','"
				+ prod.getClothes_colour() + "','"
				+ prod.getEquipment_type() + "','"
				+ prod.getEquipment_desc() + "','"
				+ prod.getGunRep_material() + "','"
				+ prod.getGunRep_caliber() + "')";
		System.out.println("insert : " + query);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(SQLException ex)
		{
			System.out.println("Product ikke oprettet");
			throw new Exception("Product is not inserted correct");
		}
		return (rc);
	}

	@Override
	public int updateProduct(Product prod) 
	{
		Product prodObj = prod;
		int rc = -1;
		String query = "UPDATE product SET " 
				+ "name = '" + prodObj.getName() + "',"
				+ "description ='" + prodObj.getDescription() + "', "
				+ "status ='" + prodObj.getStatus() + "', "
				+ "storage ='" + prodObj.getStorage() + "', "
				+ "price ='" + prodObj.getPrice() + "', "
				+ "supplierID ='" + String.valueOf(prodObj.getSupplier().getId()) + "', "
				+ "clothes_size ='" + prodObj.getClothes_colour() + "', "
				+ "clothes_colour ='" + prodObj.getClothes_colour() + "', "
				+ "equipment_type ='" + prodObj.getEquipment_type() + "', "
				+ "equipment_desc ='" + prodObj.getEquipment_desc() + "', "
				+ "gunRep_material ='" + prodObj.getGunRep_material() + "', "
				+ "gunRep_caliber ='" + prodObj.getGunRep_caliber() + "'";
		System.out.println("Update query:" + query);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception ex)
		{
			System.out.println("Update exception in product db: " + ex);
		}
		return (rc);
	}
	
	private ArrayList<Product> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Product> list = new ArrayList<Product>();
		String query = buildQuery(wClause);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while(results.next())
			{
				Product prodObj = new Product();
				prodObj = buildProduct(results);
				list.add(prodObj);
			}
			stmt.close();
			if(retrieveAssociation)
			{
				for(Product prodObj : list)
				{
					Supplier prodSup = prodObj.getSupplier();
					prodObj.setSupplier(prodSup);
					System.out.println("Supplier is selected");
				}
			}
		}
		catch(Exception e)
		{
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}
	
	private Product singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Product prodObj = new Product();
		String query = buildQuery(wClause);
		System.out.println(query);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
			{
				prodObj = buildProduct(results);
				stmt.close();
				
				if(retrieveAssociation)
				{
					Supplier prodSup = prodObj.getSupplier();
					prodObj.setSupplier(prodSup);
					System.out.println("Supplier is selected");
				}
			}
			else
			{
				prodObj = null;
			}
		}
		catch(Exception e)
		{
			System.out.println("Query exception: " + e);
		}
		return prodObj;
	}
	
	private String buildQuery(String wClause)
	{
		String query = "SELECT * FROM product";
		
		if(wClause.length() > 0)
		{
			query = query + " WHERE " + wClause;
		}
		return query;
	}
	
	private Product buildProduct(ResultSet results)
	{
		Product prodObj = new Product();
		
		try
		{
			prodObj.setName(results.getString("name"));
			prodObj.setDescription(results.getString("description"));
			prodObj.setStatus(results.getString("status"));
			prodObj.setStorage(results.getString("storage"));
			prodObj.setPrice(Double.parseDouble(results.getString("price")));
			prodObj.setSupplier(new Supplier(Integer.parseInt(results.getString("supplierID"))));
			prodObj.setClothes_size(results.getString("clothes_size"));
			prodObj.setClothes_colour(results.getString("clothes_colour"));
			prodObj.setEquipment_type(results.getString("equipment_type"));
			prodObj.setEquipment_desc(results.getString("equipment_desc"));
			prodObj.setGunRep_material(results.getString("gunRep_material"));
			prodObj.setGunRep_caliber(results.getString("gunRep_caliber"));
		}
		catch(Exception e)
		{
			System.out.println("error in building the product object");
		}
		return prodObj;
	}
}
